package EndDate;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import Inventory.InventoryRec;

public class EndDate extends HttpServlet {
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {	

		try {
			List<ProductCount> item = new ArrayList<ProductCount>();			
			
			SimpleDateFormat dt1 = new SimpleDateFormat("yyyy-MM-dd");
			String today = dt1.format(new Date());			
			
			Class.forName("org.h2.Driver");
			int DailySales = 0 ;
			double DailyTotal = 0.0;
			double DailyTotalCost = 0.0;
			double DailyNetProfit = 0.0;
			Connection con = DriverManager.getConnection("jdbc:h2:~/retail","sa", "");
			PreparedStatement ps = 
					con.prepareStatement("select count(*) from (select distinct orderid from orders WHERE cast(TRANSACTION_TIME as varchar(10)) = ?)");
			ps.setString(1, today);
			ResultSet rs = ps.executeQuery();			
						
			if(rs.next()){
				DailySales= rs.getInt(1);
			}
			
			ps = con.prepareStatement("SELECT SUM(SOLD_PRICE) FROM ORDERS  WHERE cast(TRANSACTION_TIME as varchar(10)) = ?");
			ps.setString(1, today);
			rs = ps.executeQuery();
			
			if(rs.next()){
				DailyTotal = rs.getDouble(1);
			}
			
			ps = con.prepareStatement("SELECT SUM(COST_PRICE * ORDERS.QUANTITY) FROM ORDERS left join PRODUCT ON ORDERS.PRODUCTID = PRODUCT.PRODUCTID WHERE cast(TRANSACTION_TIME as varchar(10)) = ?");
			ps.setString(1, today);
			rs = ps.executeQuery();
			
			if(rs.next()){
				DailyTotalCost = rs.getDouble(1);
			}						

			DailyNetProfit = DailyTotal - DailyTotalCost;		
			
			ps = con.prepareStatement("SELECT PRODUCTID, SUM(QUANTITY ) AS AMOUNT FROM ORDERS WHERE TRANSACTION_TIME = ? GROUP BY PRODUCTID ORDER BY AMOUNT DESC");
			ps.setString(1, today);
			rs = ps.executeQuery();
			
			while (rs.next()) {
				ProductCount rec = new ProductCount();
				rec.setProdID(rs.getString("PRODUCTID"));
				rec.setAmount(rs.getInt("Amount"));
				
				item.add(rec);
			} 			
			ps.close();
			con.close();
			
			req.setAttribute("ProductList", item);
			req.setAttribute("Today", today);
			req.setAttribute("DailySales", DailySales);
			req.setAttribute("DailyTotal",DailyTotal );
			req.setAttribute("DailyTotalCost",DailyTotalCost );
			req.setAttribute("DailyNetProfit",DailyNetProfit );
			req.getRequestDispatcher("EndDate/EndDate.jsp").forward(req, resp);			
			
		}catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	
				
	}

	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		
	}

}
